package ghr.day0109.travelt.dao.impl;


import ghr.day0109.travelt.dao.RouteDao;
import ghr.day0109.travelt.entity.TourRoute;
import ghr.day0109.travelt.utils.BaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RouteDaoImpl extends BaseDao implements RouteDao  {
    @Override
    public List<TourRoute> queryAllTourRoute() {
        List<TourRoute> routeList = new ArrayList<>();
        TourRoute tour = null;
        String sql = "select tr_id,tr_name,tr_type,tr_price,tr_phone,tr_user, to_char(create_time,'yyyy-mm-dd') create_time  from tour_route";
        super.executeQuery(sql);
        try {
            while (rs.next()) {
                tour = new TourRoute();
                tour.setTrID(rs.getInt("tr_id"));
                tour.setTrName(rs.getString("tr_name"));
                tour.setTrType(rs.getInt("tr_type"));
                tour.setTrPrice(rs.getInt("tr_price"));
                tour.setTrPhone(rs.getString("tr_phone"));
                tour.setTrUser(rs.getString("tr_user"));
                tour.setCreateTime(rs.getString("create_time"));
                routeList.add(tour);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return routeList;
    }

    @Override
    public List<TourRoute> queryOneTourRoute(String name) {
        List<TourRoute> routeList = new ArrayList<>();
        TourRoute tour = null;
        String sql = "select tr_id,tr_name,tr_type,tr_price,tr_phone,tr_user, to_char(create_time,'yyyy-mm-dd') create_time  from tour_route " +
                " where tr_name = ? ";
        super.executeQuery(sql,new Object[]{name});
        try {
            while (rs.next()) {
                tour = new TourRoute();
                tour.setTrID(rs.getInt("tr_id"));
                tour.setTrName(rs.getString("tr_name"));
                tour.setTrType(rs.getInt("tr_type"));
                tour.setTrPrice(rs.getInt("tr_price"));
                tour.setTrPhone(rs.getString("tr_phone"));
                tour.setTrUser(rs.getString("tr_user"));
                tour.setCreateTime(rs.getString("create_time"));
                routeList.add(tour);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return routeList;
    }

    @Override
    public int addRoute(TourRoute tour) {
        String sql ="insert into tour_route(tr_id,tr_name,tr_type,tr_price,tr_phone,tr_user,create_time) values\n" +
                "(tr_id_seq.nextval,?,?,?,?,?,TO_date('2021-05-16','YYYY-MM-DD'))";
        return super.executeUpdate(sql,new Object[]{tour.getTrName(),tour.getTrType(),tour.getTrPrice(),tour.getTrPhone(),
                                        tour.getTrUser()});
    }

    @Override
    public int deleteRoute(TourRoute tour) {
        String sql =" delete from tour_route where tr_id=?";
        return super.executeUpdate(sql,new Object[]{tour.getTrID()});
    }

}
